<?php

namespace org\copperforest\oak\resources\factories;
use \org\copperforest\oak as oak;
/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of SQLDataSource
 *
 * @author agama
 */
class SqlFactory extends ResponseFactory
{
    protected $select = '';
    protected $query = '';
    protected $end = '';
    protected $order = array();
    protected $idNumeric = null;
    protected $idFields = null;
    
    public function setConfiguration( $config, $backtrace=array() )
    {
        $link = oak\Oak::pdo();
        
        parent::setConfiguration( $config, $backtrace );

        foreach( $this->config[ 'AuthorizedResponses' ] as $class => $athorization ){
                        
            $appendGlue = false;
            $authorizedString ='';

            foreach( $athorization[ 'AuthorizedString' ] as $typeValue ){

                if( !$appendGlue ){
                    $appendGlue = true;

                    if( $typeValue[ 0 ] == 'Text' ){
                        $authorizedString = $link->helper->quote( $typeValue[ 1 ]  );
                    }
                    else if( $typeValue[ 0 ] == 'Field' ){
                        $authorizedString =  $link->helper->quoteIdentifier( $typeValue[ 1 ] );
                    }
                }
                else{

                    if( $typeValue[ 0 ] == 'Text' ){
                        $authorizedString .=  ',' . $link->helper->quote(  $typeValue[ 1 ] );
                    }
                    else if( $typeValue[ 0 ] == 'Field' ){
                        $authorizedString .=   ', ' . $link->helper->quoteIdentifier( $typeValue[ 1 ] );
                    }
                }    
            }

            $authorizedString = 'CONCAT_WS(' . $link->helper->quote( $athorization[ 'Glue' ] ) . ', ' . $authorizedString . ')';
            

            $appendGlue = false;

            $groupString = '';
            foreach( $athorization[ 'Groups' ] as $group => $levels ){
                $groupString .= ( ',' . $link->helper->quote( $group ) );
            }

            //$select[] = 'FIELD('. $authorizedString . $groupString . ')>0 THEN '. $link->helper->quote( substr($class,1) );
            $select[] = 'FIELD('. $authorizedString . $groupString . ')>0 THEN '. $link->helper->quote( $class );
            $where[] = 'FIELD('. $authorizedString . $groupString . ')>0';


        }
        
        if( !empty( $select ) ){
            //Sobra un $select con ponerlo en el where y utilizarlo de caso base
            $this->select = 'CASE WHEN ' . implode( ' ELSE WHEN ', $select ) .' END className, ' .$config[ 'Select' ];
            $this->query = $config[ 'FromWhere' ] . ' AND (' . implode(' OR ', $where ) . ')';
            $this->end = $config[ 'End' ];
            $this->idFields = $config[ 'Id' ];
            $this->idNumeric = $config[ 'IdNumeric' ]; 
        }
        else{
            throw  new \org\copperforest\oak\http\exceptions\ForbiddenException();
        }
                    
    }


    public function fetchArray( $string='', $num = 10, $page = 1, $order = array(), $group = null, &$count=false )  //$group is experimental
    {
        $query = $this->query;
        
        if( !empty( $string ) ){
            $query .= ' AND (' . $string . ')';
        }
        
        if ( !empty( $this->end ) ){
            $query .= ' ' . $this->end;
        }
        
        $orderSQL = '';
        if ( !empty( $order ) ) {
            
            
            $comma = false;
            foreach ( $order as $field=>$ord ) {

                if ( $ord != 'U' ) {

                    if ( $comma ){
                        $orderSQL .= ', ';
                    }
                    else{
                        $comma = true;
                    }

                    $orderSQL .=  $field;

                    if( $ord == 'D' ){
                        $orderSQL .= ' DESC';
                    }
                }
            }
            
            if( !empty($orderSQL) ){
                $orderSQL = ' ORDER BY ' .$orderSQL;
            }
        }

        
        $calculateTotal = ( $count!==false && ( $page == 1 || is_int( $count ) && ( $count / $num -2 ) < $page ) );
        
        if (  $page > 1 ) { //tiene que estar num tb?..no
            if ( oak\Oak::pdo()->helper->isLIMITAvailable() ) {
                
                $select = 'SELECT '. ($calculateTotal?'SQL_CALC_FOUND_ROWS ':'') . $this->select . $query;

                if ( !empty( $orderSQL ) ){
                    $query .= $orderSQL;
                }

                $select .=' LIMIT ' . $page * $num . ',' . $num;
            }
            else{
                //http://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-server-paging-methods/
                //segun el blog anterior las optimizaciones de sql 2012 solo afectan a la primera pagina, o lo mismo que si fuera offset a 0 y TOP
                $select  = 'SELECT TOP(' . $num . ') * FROM ( SELECT ' . $this->select . ', ROW_NUMBER() OVER (' . $orderSQL . ') AS [R0wNumb3r] ' . $query . ') AS [P4g3dT4bl3] WHERE [R0wNumb3r] >' . $page * $num;
            }
        } else {

            if ( oak\Oak::pdo()->helper->isLIMITAvailable() ){
                $select = 'SELECT '. ($calculateTotal?'SQL_CALC_FOUND_ROWS ':'') . $this->select . ' FROM ' . $query . ( empty( $orderSQL ) ? '' : $orderSQL) . ' LIMIT ' . $num;
            }
            else{
                $select  = 'SELECT TOP(' . $num . ') ' . $this->select . ' FROM ' . $query . ( empty( $orderSQL ) ? '' : $orderSQL );
            }
        }
        
        
        $stmnt = oak\Oak::pdo()->query( $select );
        $stmnt->execute();
        $stmnt->setFetchMode( \PDO::FETCH_CLASS | \PDO::FETCH_CLASSTYPE );

        $eltos= $stmnt->fetchAll();
        $stmnt->closeCursor();
        
        if( $calculateTotal ){
            
            if ( oak\Oak::pdo()->helper->isLIMITAvailable() ){
                $stmnt = oak\Oak::pdo()->query( 'SELECT FOUND_ROWS()', \PDO::FETCH_COLUMN, 0 );
            }
            else{
                $stmnt = oak\Oak::pdo()->query( 'SELECT COUNT(1) ' . $query );
            }
            
            $count = $stmnt->fetch( \PDO::FETCH_COLUMN, 0 );
            $stmnt->closeCursor();
        }
        
        return $eltos;
    }

    public function fetchInstance( $id = null )
    {
        
        $query = $this->query;

        //Bien, tengo que saber cuales son los identificadoers.
        for( $i=0; $i < count( $id ); $i++ ) {
            $query .= ' AND ' . $this->idFields[ $i ] . '=' . ( $this->idNumeric[ $i ] ? ( (float) $id[ $i ] ) : oak\Oak::pdo()->helper->quote( $id[ $i ] ) );
        }
 
        if ( !empty( $this->end ) ){
            $query .= ' ' . $this->end;
        }
        
        //AQUI CON PREPARE NO FUNCIONA, ¿¿??
        $stmnt = oak\Oak::pdo()->query( 'SELECT ' .$this->select . ' FROM '. $query );
        $response = $stmnt->fetch( \PDO::FETCH_CLASS | \PDO::FETCH_CLASSTYPE );
        $stmnt->closeCursor();
        
        if( empty( $response ) ){
            throw new \org\copperforest\oak\http\exceptions\ForbiddenException();
        }
        
        return $response;
    }

    
    static function parseFactory( \SimpleXMLElement $xml )
    {
        $config = parent::parseFactory( $xml );
        
                //Lo primero tendría que ser buscar de que tabla son
        $connectionSchema = oak\Oak::pdo()->getSchema();
        $connectionCatalog= oak\Oak::pdo()->getCatalog();
        
        $sqlParser = new oak\sql\SQLParser( oak\Oak::pdo()->getEngine() );

        $select = $sqlParser->exploreSELECT( (string) $xml->ResponseFactory->Query );

        $config[ 'Begin' ] = $select[ 'reserved' ];
        $config[ 'FromWhere' ] = $select[ 'from' ] . ' WHERE ' . ( empty( $select[ 'where' ] ) ? '1' : $select[ 'where' ] );
        $config[ 'Order' ] = explode( ',', $select[ 'order by' ] );
        $config[ 'End' ] = $select[ 'group by' ];
        $config[ 'Id' ] = array();

        foreach( $select[ 'tables' ] as &$t ){
            
            $t[ 'columns' ] = array();
            
            $result = oak\Oak::pdo()->query( oak\Oak::pdo()->helper->getColumnsSQL( $t['table'], empty( $t[ 'schema' ] ) ? $connectionSchema : $t[ 'schema' ] ) );
            
            while( $column = $result->fetch( \PDO::FETCH_NUM ) ){
                $t[ 'columns' ] [ $column[ 0 ] ] = oak\Oak::pdo()->helper->isNumeric( $column[ 1 ] );
            }
            $result->closeCursor();
        }
        /*
        $result = oak\Oak::pdo()->query( oak\Oak::pdo()->helper->getTablesSQL() );
        while( $table= $result->fetch( \PDO::FETCH_ASSOC ) ){
            $tables[] = $table; //TABLE_SCHEMA, TABLE_NAME
        }
        $result->closeCursor();
        */
        
        $config[ 'Select' ] = '';
        //faltan estas dos por configurar
        $config[ 'IdNumeric' ] = array();
        
        $appendGlue = false;
        
        
        $idIndex = 0;
        //$select['fields']; //array de ternas ('catalog' =>, 'schema' =>, 'table' =>, 'field' =>, 'alias' =>, 'field_org' => )
        
        foreach( $select[ 'fields' ] as &$f ){
            //obligo a marcarlos con el alias id en la Query con lo que puedo ver que campos forman el id de la consulta
            if( !empty( $f[ 'alias' ] ) && $f[ 'alias' ] != 'id' && $f[ 'alias' ] != 'id[]' || empty( $f[ 'alias' ] ) && $f[ 'field_org' ] != 'id' ){
                
                if( $appendGlue ){
                    $config[ 'Select' ] .= ',';
                }
                else{
                    $appendGlue = true;
                }
                
                $config[ 'Select' ] .= $f[ 'field_org'];
                
            }
            else{
                
                if( $appendGlue ){
                    $config[ 'Select' ] .= ',';
                }
                else{
                    $appendGlue = true;
                }
                
                
                $field_org = '';
                if( !empty( $f[ 'catalog' ] ) ){
                    $field_org .= oak\Oak::pdo()->helper->quoteIdentifier( $f[ 'catalog' ] ) . '.';
                }
                if( !empty( $f[ 'schema' ] ) ){
                    $field_org .= oak\Oak::pdo()->helper->quoteIdentifier( $f[ 'schema' ] ) . '.';
                }
                if( !empty( $f[ 'table' ] ) ){
                    $field_org .= oak\Oak::pdo()->helper->quoteIdentifier( $f[ 'table' ] ) . '.';
                }
                
                $field_org .= oak\Oak::pdo()->helper->quoteIdentifier( $f[ 'field' ] );
                
                foreach( $select[ 'tables' ] as $t ) {
                    $buscarCampo = false;
                    
                    if( empty( $f['table'] ) ) {   
                        $buscarCampo = true;
                    }
                    else if( empty( $f[ 'schema' ] ) ){
                        $buscarCampo = (  
                                $f['table'] == $t[ 'alias' ] || 
                                $f[ 'table' ] == $t[ 'table' ] &&
                                ( empty( $t[ 'schema' ] ) || $t['schema'] == $connectionSchema ) &&
                                ( empty( $t[ 'catalog' ] ) || $t['catalog'] == $connectionCatalog )
                                );
                    }
                    else if( empty( $f[ 'catalog' ] ) ){ //si el campo tienen esquema $f['table'] no puede ser un alias
                        $buscarCampo = (  
                                $f[ 'table' ] == $t[ 'table' ] &&
                                ( empty( $t[ 'schema' ] ) && $f[ 'schema' ] == $connectionSchema  || $t['schema'] == $f[ 'schema' ] ) &&
                                ( empty( $t[ 'catalog' ] ) || $t['catalog'] == $connectionCatalog )
                                );
                    }
                    else{
                        $buscarCampo = (  
                                $f[ 'table' ] == $t[ 'table' ] &&
                                ( empty( $t[ 'schema' ] ) && $f[ 'schema' ] == $connectionSchema || $t['schema'] == $f[ 'schema' ]  ) &&
                                ( empty( $t[ 'catalog' ] ) && $f['catalog'] == $connectionCatalog || $t['catalog'] == $f['catalog'] )
                                );
                    }
                    
                    if( $buscarCampo ){
                        foreach( $t[ 'columns' ] as $c => $isNumeric ) {
                            if( $c == $f[ 'field' ] ){
                                $config[ 'Id' ][] = $field_org;
                                $config[ 'IdNumeric' ][] = $isNumeric;
                                break 2;
                            }
                        }
                    }
                }
                
                //los campos ids no pueden llamarse igual, tendrían que ser id[0], id[1],... etc
                $config[ 'Select' ] .= $field_org . ' ' . oak\Oak::pdo()->helper->quoteIdentifier( 'id[' . $idIndex . ']' ); //cambiamos el nombre para que no coincida con la propiedad id y lo asigne directamente
                
                $idIndex++;
            }
        }
        
        
        return $config;
    }
//put your code here
}
